package com.campus.dao.impl;


import com.campus.dao.CourseLearnUnitDao;
import com.campus.model.CourseLearnUnit;
import com.campus.model.UnitCompletion;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;

@Repository
@Slf4j
public class CourseLearnUnitDaoImpl implements CourseLearnUnitDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int createCourseLearnUnit(CourseLearnUnit unit) {
        String sql = "INSERT INTO t_course_learn_unit (unit_title, unit_introduce, father_id, is_chpt, course_id, learning_score) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql, unit.getUnitTitle(), unit.getUnitIntroduce(),
                unit.getFatherId(), unit.getIsChpt(), unit.getCourseId(), unit.getLearningScore());
        // 获取自增长的 ID
        return jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);
    }

    @Override
    public Integer getClassIdByCourseId(int courseId) {
        String sql = "SELECT class_id FROM t_course WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, courseId);
    }

    @Override
    public List<Integer> getStudentInClassIdsByClassId(int classId) {
        String sql = "SELECT id FROM t_teaching_student_in_class WHERE class_id = ?";
        return jdbcTemplate.queryForList(sql, Integer.class, classId);
    }

    @Override
    public int insertStudentLearningBatch(int unitId, List<Integer> studentInClassIds) {
        String sql = "INSERT INTO t_teaching_student_learning (stu_in_class_id, unit_id, finished_status, finished_time) VALUES (?, ?, '0', NOW())";
        List<Object[]> batchArgs = studentInClassIds.stream()
                .map(stuId -> new Object[]{stuId, unitId})
                .toList();
        return Arrays.stream(jdbcTemplate.batchUpdate(sql, batchArgs)).sum();
    }

    @Override
    public int deleteCourseLearnUnit(int unitId) {
        String deleteStudentLearningSql = "DELETE FROM t_teaching_student_learning WHERE unit_id = ?";
        jdbcTemplate.update(deleteStudentLearningSql, unitId);

        String deleteUnitSql = "DELETE FROM t_course_learn_unit WHERE id = ?";
        return jdbcTemplate.update(deleteUnitSql, unitId);
    }

    @Override
    public int updateCourseLearnUnit(CourseLearnUnit unit) {
        String sql = "UPDATE t_course_learn_unit SET unit_title = ?, unit_introduce = ?, father_id = ?, is_chpt = ?, course_id = ?, learning_score = ? WHERE id = ?";
        return jdbcTemplate.update(sql, unit.getUnitTitle(), unit.getUnitIntroduce(), unit.getFatherId(),
                unit.getIsChpt(), unit.getCourseId(), unit.getLearningScore(), unit.getId());
    }

    @Override
    public List<CourseLearnUnit> listAllCourseLearnUnits() {
        String sql = "SELECT * FROM t_course_learn_unit";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CourseLearnUnit.class));
    }

    @Override
    public List<UnitCompletion> getAllUnitsCompletion() {
        // SQL 查询所有学习单元的完成度
        String sql = """
                SELECT 
                    u.id,
                    IFNULL(
                        (SUM(CASE WHEN t.finished_status = '1' THEN 1 ELSE 0 END) * 1.0 / COUNT(t.id)), 
                        0
                    ) AS completionRate
                FROM t_course_learn_unit u
                LEFT JOIN t_teaching_student_learning t ON u.id = t.unit_id
                GROUP BY u.id
                """;

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UnitCompletion.class));
    }

}
